package com.dy.dao;

import com.dy.bean.Sugar;
import com.dy.bean.SugarT;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("SELECT b2.name 'name',sum(o.final_total_amount)'value' FROM order_info o \n" +
            "LEFT JOIN order_detail d on o.id = d.order_id\n" +
            "LEFT JOIN sku_info s on d.sku_id = s.id\n" +
            "LEFT JOIN spu_info si on s.spu_id = si.id \n" +
            "LEFT JOIN base_category3 b3 on si.category3_id = b3.id \n" +
            "LEFT JOIN base_category2 b2 on b3.category2_id = b2.id \n" +
            "GROUP BY b2.name ORDER BY value asc")
    List<Sugar> showTwo();

    @Select("SELECT CONCAT(SUBSTRING(birthday,3,1),'0后')'name',sum(o.final_total_amount) 'value'FROM user_info u \n" +
            "LEFT JOIN order_info o on u.id = o.user_id\n" +
            "GROUP BY CONCAT(SUBSTRING(birthday,3,1),'0后')")
    List<Sugar> showAge();

    @Select("SELECT yys'name',sum(final_total_amount)'value' FROM order_info WHERE yys is not null GROUP BY yys")
    List<Sugar> showYys();

    @Select("SELECT t.name'name',if(u.gender='F','男','女')'gender',sum(final_total_amount)'value' FROM user_info u \n" +
            "LEFT JOIN order_info o on u.id = o.user_id\n" +
            "LEFT JOIN order_detail d on d.order_id = o.id \n" +
            "LEFT JOIN sku_info s on s.id = d.sku_id\n" +
            "LEFT JOIN base_trademark t on s.tm_id = t.id \n" +
            "where t.name is not null\n" +
            "GROUP BY t.name,if(u.gender='F','男','女')")
    List<SugarT> showPp();
}
